import paramiko
from dateutil.relativedelta import relativedelta
import datetime
from config import config


# sqoop cmd
class SqoopCmd(object):
    def __init__(self, db_ip, db_port, db_db, user, passwd, sql, fields_terminated_by, lines_terminated_by, split_by,
                 num_mappers, is_overwrite, save_path, increase_type, increase_column, increase_data_type, source_db):

        today = datetime.datetime.now()
        yestoday = datetime.datetime.now() - datetime.timedelta(days=1)
        last_month = today - relativedelta(months=1)

        today_str = today.strftime('%Y-%m-%d')
        today_str += ' 00:00:00'
        yestoday_str = yestoday.strftime('%Y-%m-%d')
        yestoday_str += ' 00:00:00'

        this_month_str_all = today_str[:8] + '01 00:00:00'

        last_month_str = last_month.strftime('%Y-%m')
        last_month_str_all = last_month_str + '-01 00:00:00'

        today_date = datetime.datetime.strptime(today_str ,'%Y-%m-%d %H:%M:%S')
        yestoday_date = datetime.datetime.strptime(yestoday_str, '%Y-%m-%d %H:%M:%S')

        last_month_date = datetime.datetime.strptime(last_month_str_all ,'%Y-%m-%d %H:%M:%S')
        this_month_date = datetime.datetime.strptime(this_month_str_all ,'%Y-%m-%d %H:%M:%S')

        self.yestoday = yestoday.strftime('%Y-%m-%d')
        self.last_month = last_month_str

        self.year = yestoday.year
        self.month = yestoday.month
        self.day = yestoday.day

        if source_db == 2:
            self.connect = config.SQOOP_SQLSERVER.format(db_ip, db_db)
        elif source_db == 3:
            self.connect = config.SQOOP_ORACLE.format(db_ip, db_port, db_db)
        else:
            self.connect = config.SQOOP_MYSQL.format(db_ip, db_port, db_db)

        sqoop_cmd = "sqoop import --connect '{}' --username {} --password {}  --fields-terminated-by '{}' " \
                    "--lines-terminated-by '{}' --split-by {} --num-mappers {}". \
            format(self.connect, user, passwd, fields_terminated_by, lines_terminated_by, split_by, num_mappers)

        self.is_overwrite = is_overwrite
        self.save_path = save_path

        sql_split = sql.split(' ')
        if len(sql_split) > 1:
            if increase_type == 2:
                sql = '{} and {} = {}'.format(sql, increase_column, self.year)
            if increase_column == 3:
                if increase_data_type == 1:
                    sql = '{} and {} >= {} and {} < {}'.format(sql, increase_column, last_month_date,
                                                               increase_column, this_month_date)
                else:
                    sql = '{} and {} = {}'.format(sql, increase_column, self.last_month)
            if increase_column == 4:
                if increase_data_type == 1:
                    sql = '{} and {} >= {} and {} < {}'.format(sql, increase_column, yestoday_date,
                                                               increase_column, today_date)
                else:
                    sql = '{} and {} = {}'.format(sql, increase_column, self.yestoday)

            sql = '{} and \\$CONDITIONS'.format(sql)
            self.sqoop_cmd = sqoop_cmd + " --query '{}'".format(sql)
            self.query_sql = True
        else:
            if increase_type == 2:
                sql = 'select * from {} where {} = {}'.format(sql, increase_column, self.year)
            if increase_column == 3:
                sql = 'select * from {} where and {} >= {} and {} < {}'.\
                    format(sql, increase_column, last_month_date, increase_column, this_month_date)
            if increase_column == 4:
                sql = 'select * from {} where and {} >= {} and {} < {}'.\
                    format(sql, increase_column, yestoday_date, increase_column, today_date)

            if increase_column in [2, 3, 4]:
                sql = '{} and \\$CONDITIONS'.format(sql)
                self.sqoop_cmd = sqoop_cmd + " --query '{}'".format(sql)
                self.query_sql = True
            else:
                self.sqoop_cmd = sqoop_cmd + ' --table {}'.format(sql)
                self.query_sql = False

    def sqoop_hcatalog(self, hive_db, hive_table, partition):
        hive_db = hive_db
        hive_table = hive_table

        hcatalog_storage_stanza = '\'stored as orc tblproperties (\\"orc.compress\\"=\\"NONE\\")\''

        sqoop_cmd = self.sqoop_cmd + ' --hcatalog-database {} --hcatalog-table {}'.format(hive_db, hive_table)

        # 覆盖
        if self.is_overwrite:
            sqoop_cmd += ' --drop-and-create-hcatalog-table'

        # 分区
        if partition == 2:
            sqoop_cmd += ' --hcatalog-partition-keys year --hcatalog-partition-values {}'.format(self.year)
        elif partition == 3:
            sqoop_cmd += ' --hcatalog-partition-keys year,month --hcatalog-partition-values {},{}'.\
                format(self.year, self.month)
        elif partition == 4:
            sqoop_cmd += ' --hcatalog-partition-keys year,month,day --hcatalog-partition-values ' \
                              '{},{},{}'.format(self.year, self.month, self.day)

        # 存储路径
        if self.save_path:
            sqoop_cmd += ' --target-dir {}'.format(self.save_path)

        sqoop_cmd += ' --hcatalog-storage-stanza {}'.format(hcatalog_storage_stanza)
        return sqoop_cmd
        #         return 'su - hive -c  "{}"'.format(sqoop_cmd)

    def sqoop_hdfs_path(self, path, stored):
        # 覆盖
        sqoop_cmd = self.sqoop_cmd
        if self.is_overwrite:
            sqoop_cmd += ' --hive-overwrite --delete-target-dir'

        # 存储路径
        sqoop_cmd += ' --target-dir {}'.format(path)

        sqoop_cmd += ' {}'.format(stored)
        return sqoop_cmd
        #         return 'su - hive -c  "{}"'.format(sqoop_cmd)

    def sqoop_hdfs(self, stored):
        # 覆盖
        sqoop_cmd = self.sqoop_cmd
        if self.is_overwrite:
            sqoop_cmd += ' --hive-overwrite --delete-target-dir'

        # 存储路径
        sqoop_cmd += ' --target-dir {}'.format(self.save_path)

        sqoop_cmd += ' {}'.format(stored)
        return sqoop_cmd
        #  return 'su - hive -c  "{}"'.format(sqoop_cmd)

    def sqoop_hive(self, stored, hive_db, hive_table, partition):
        sqoop_cmd = self.sqoop_cmd + ' --hive-import --hive-database {} --hive-table {}'.\
            format(hive_db, hive_table)

        # 覆盖
        if self.is_overwrite:
            sqoop_cmd += ' --hive-overwrite'
        else:
            sqoop_cmd += ' --create-hive-table'

        # 存储路径
        if self.save_path:
            target_dir = self.save_path
        else:
            target_dir = '/user/hive/warehouse/{}.db/{}'.format(hive_db, hive_table)

        # 分区
        if partition == 2:
            target_dir += '/year={}'.format(self.year)
        elif partition == 3:
            target_dir += '/year={}/month={}'.format(self.year, self.month)
        elif partition == 4:
            target_dir += '/year={}/month={}/day={}'.format(self.year, self.month, self.day)

        if partition in [2, 3, 4]:
            print('target_dir: {}'.format(target_dir))
            return self.sqoop_hdfs_path(path=target_dir, stored=stored)

        if not partition and self.save_path:
            sqoop_cmd += ' --target-dir {}'.format(self.save_path)
        if not partition and self.query_sql:
            sqoop_cmd += ' --target-dir {}'.format(target_dir)

        sqoop_cmd += ' --delete-target-dir {}'.format(stored)
        return sqoop_cmd


# 远程ssh执行命令
def sshclient_exec(cmd, env='DVE'):
    try:
        s = paramiko.SSHClient()
        s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        if env == 'PDE':
            print(env)
            s.connect(hostname=config.SSH_PDE_HOST, port=config.SSH_PDE_PORT, username=config.SSH_PDE_USER,
                      password=config.SSH_PDE_PASSWORD)
        else:
            print(env)
            s.connect(hostname=config.SSH_DEV_HOST, port=config.SSH_DEV_PORT, username=config.SSH_DEV_USER,
                      password=config.SSH_DEV_PASSWORD)

        stdin, stdout, stderr = s.exec_command(cmd)
        for line in stdout:
            print(line.strip())

        for line in stderr:
            print(line.strip())
        s.close()
    except Exception as e:
        msg = '登陆服务器异常'
        print(msg)
        print(e)
